Multidimensional cube functions

ABSTRACT

Cube functions may be used to obtain data from a multidimensional database. The cube functions may be contained within one or more cells of a spreadsheet. These cube functions behave similarly to the standard functions that may be included within a spreadsheet. Exemplary cube functions include obtaining: a cube member, a cube value, a cube set, a ranked member, a KPI, a member property and a count relating to a set. The cube functions within the spreadsheet may access the cube data from one or more multidimensional databases. Using the cube formulas in individual cells allows the user to add/delete rows and/or columns from within the spreadsheet.

BACKGROUND

Spreadsheet software applications are used by many different users formanipulating data. Typical spreadsheet applications simulate physicalspreadsheets by capturing, displaying, and manipulating data arranged inrows and columns. In addition to using spreadsheet applications, manyusers also store and utilize enormous amounts of data stored inmultidimensional databases. These multidimensional databases are alsoknown as OLAP cubes. These OLAP cubes are architecturally different fromrelational databases or object oriented databases and the language usedto query and describe elements within the OLAP cubes is theMulti-Dimensional eXpression (MDX) language. OLAP systems analyze datadrawn from other databases, often large relational databases such asdata warehouses, or other multidimensional databases. The purpose ofsuch analysis is to aggregate and organize business information into areadily accessible, easy to use multidimensional structure. Placing thismultidimensional data within a PivotTable report within a spreadsheet islimiting because PivotTable reports are restrictive in their layout.

SUMMARY

This Summary is provided to introduce a selection of concepts in asimplified form that are further described below in the DetailedDescription. This Summary is not intended to identify key features oressential features of the claimed subject matter, nor is it intended tobe used as an aid in determining the scope of the claimed subjectmatter.

Cube functions may be used to obtain data from a multidimensionaldatabase. The cube functions may be contained within one or more cellsof a spreadsheet and/or may be accessed through an API. The cubefunctions behave similarly to the standard functions that may beincluded within cells of a spreadsheet. Exemplary cube functionsinclude, obtaining: a cube member, a cube value, a cube set, a rankedmember, a KPI, a member property and a count of items in a set. Data maybe obtained from one or more multidimensional databases. The cubefunctions may be executed asynchronously such that a user may continueto interact with the spreadsheet while the requested data is beingobtained from one or more of the multidimensional databases. The cubefunctions may take parameters that may include other spreadsheet cellreferences as well as other cube functions. The use of the cubefunctions enables the spreadsheet program to be used as a reporting toolfor multidimensional (OLAP) data stores. Using the cube formulas inindividual cells allows the user to add/delete rows and/or columns fromwithin the spreadsheet.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates an exemplary computing architecture for a computer;

FIG. 2 illustrates an overview of a spreadsheet system for accessingmultidimensional data through the use of cube functions;

FIG. 3 illustrates a system for interfacing with OLAP cubes from cellsof a spreadsheet application;

FIG. 4 illustrates cube functions that may be utilized to connect with amultidimensional database;

FIG. 5 displays an operational flow for using cube functions within aspreadsheet; and

FIGS. 6 and 7 show an exemplary spreadsheet that includes cube functionsbefore evaluation and after evaluation, in accordance with aspects ofthe present invention.

DETAILED DESCRIPTION

Referring now to the drawings, in which like numerals represent likeelements, various aspects of the present invention will be described. Inparticular, FIG. 1 and the corresponding discussion are intended toprovide a brief, general description of a suitable computing environmentin which embodiments of the invention may be implemented.

Generally, program modules include routines, programs, components, datastructures, and other types of structures that perform particular tasksor implement particular abstract data types. Other computer systemconfigurations may also be used, including hand-held devices,multiprocessor systems, microprocessor-based or programmable consumerelectronics, minicomputers, mainframe computers, and the like.Distributed computing environments may also be used where tasks areperformed by remote processing devices that are linked through acommunications network. In a distributed computing environment, programmodules may be located in both local and remote memory storage devices.

Throughout the specification and claims, the following terms take themeanings explicitly associated herein, unless the context clearlydictates otherwise. The term “MDX” refers to the MultiDimensionaleXpressions language. The term “KPI” refers to a Key PerformanceIndicator. The term “MDX Name” is a name as defined by MDX. The MDXunique name of a member is generally in the form[Dimension].[Hierarchy].[Level].&[MemberKey] wherein all of thesecomponents are required. Non-unique names could have several other formsincluding [Member] or [Dimension].[Member] or [Dimension].&[98765]. Theterm “caption” refers to a non-unique friendly name to be displayed inthe spreadsheet. The term “connection” refers to the name of a dataconnection that has been stored within a spreadsheet workbook.Connection names are strings that uniquely identify connections withinthe workbook in which they are used. The term “cube” refers to themulti-dimensional OLAP database from which data is retrieved. The term“member” is a value along one of the cube's dimensions. For example, amember of a Time dimension might be “June 2003”. A member of a customersdimension might be “John Doe.” The term “tuple” is the intersection ofone or more members in a cube, with only one member from each dimension.The tuple represents the slice of the cube that includes the specifiedmembers. When a tuple contains only one member then that member and thetuple are identical to each other. The MDX Name for a tuple is of theform (<member1>, <member2> . . . <memberN>) where each <member> isreplaced with the MDX name of that member. When the spreadsheet refersto a range of cells that contain members (or tuples) these ranges areinterpreted as tuples. For example, if cells A10 thru A13 of aspreadsheet contain members, then the cube function=CubeValue(“MyConnection”, $A10:$A13, D$2) has a tuple as its second argument. Theterm “set” is an ordered collection of one or more members (or tuples).

Briefly described, cube functions may be used to access data from amultidimensional database. According to embodiments, users can enter oneor more cube functions into one or more of the spreadsheet cells,specify a multidimensional database, query a selected database, and thenreturn the data to be used within the cell(s) of the spreadsheet.According to one embodiment, the data may be an aggregated value, adimension member, a KPI, or a member property. The use of these cubefunctions enables the spreadsheet program to be more easily used as areporting tool for multidimensional OLAP data stores. Using the cubefunctions within the individual cells of the spreadsheet provides fargreater layout and formatting flexibility than using other methods whichenforce limitations on the layout of the spreadsheet.

Referring now to FIG. 1, an exemplary computer architecture for acomputer 2 utilized in various embodiments will be described. Thecomputer architecture shown in FIG. 1 may be configured in manydifferent ways. For example, the computer may be configured as a server,a personal computer, a mobile computer and the like. As shown, computer2 includes a central processing unit 5 (“CPU”), a system memory 7,including a random access memory 9 (“RAM”) and a read-only memory(“ROM”) 11, and a system bus 12 that couples the memory to the CPU 5. Abasic input/output system containing the basic routines that help totransfer information between elements within the computer, such asduring startup, is stored in the ROM 11. The computer 2 further includesa mass storage device 14 for storing an operating system 16, applicationprograms, and other program modules, which will be described in greaterdetail below.

The mass storage device 14 is connected to the CPU 5 through a massstorage controller (not shown) connected to the bus 12. The mass storagedevice 14 and its associated computer-readable media providenon-volatile storage for the computer 2. Although the description ofcomputer-readable media contained herein refers to a mass storagedevice, such as a hard disk or CD-ROM drive, the computer-readable mediacan be any available media that can be accessed by the computer 2.

By way of example, and not limitation, computer-readable media maycomprise computer storage media and communication media. Computerstorage media includes volatile and non-volatile, removable andnon-removable media implemented in any method or technology for storageof information such as computer-readable instructions, data structures,program modules or other data. Computer storage media includes, but isnot limited to, RAM, ROM, EPROM, EEPROM, flash memory or other solidstate memory technology, CD-ROM, digital versatile disks (“DVD”), orother optical storage, magnetic cassettes, magnetic tape, magnetic diskstorage or other magnetic storage devices, or any other medium which canbe used to store the desired information and which can be accessed bythe computer 2.

According to various embodiments, the computer 2 operates in a networkedenvironment using logical connections to remote computers through anetwork 18, such as the Internet. The computer 2 may connect to thenetwork 18 through a network interface unit 20 connected to the bus 12.The network interface unit 20 may also be utilized to connect to othertypes of networks and remote computer systems.

The computer 2 may also include an input/output controller 22 forreceiving and processing input from a number of devices, such as: akeyboard, mouse, electronic stylus and the like. Similarly, theinput/output controller 22 may provide output to a display screen, aprinter, or some other type of device (not shown).

As mentioned briefly above, a number of program modules and data filesmay be stored in the mass storage device 14 and RAM 9 of the computer 2,including an operating system 16 suitable for controlling the operationof a networked computer, such as: the WINDOWS XP operating system fromMICROSOFT CORPORATION; UNIX; LINUX and the like. The mass storage device14 and RAM 9 may also store one or more program modules. In particular,the mass storage device 14 and the RAM 9 may store a spreadsheetapplication program 10, such as the MICROSOFT® EXCEL spreadsheetapplication. According to one embodiment, the spreadsheet application 10is operative to provide functionality for interacting with an OLAP datastore through the use of cube functions that are entered into one ormore cells of spreadsheet application 10. The spreadsheet may generateone or more requests to fetch data from an OLAP cube identified by thecube function contained within the cell and populate a cell when thereare cube functions contained within one or more cells of thespreadsheet.

The spreadsheet application 10 is configured to receive user input. Forexample, a user enters item data into a spreadsheet via a graphical userinterface. The user input can be item data, item metadata, functioninformation, cube function information, or other data. The user inputmay be direct input created by the user typing, pasting, or otherdeliberate action entering data into the spreadsheet or indirect inputthat may be generated by another program.

Calculation engine 26 performs operations relating to the cells withinthe spreadsheet. According to one embodiment, calculation engine 26 is acomponent within the spreadsheet application 10. The calculation engine26, however, may be located externally from the spreadsheet application10. The operations performed by calculation engine 26 may bemathematical, such as summation, division, multiplication, etc., or mayinclude other functions or features, such as interacting with amultidimensional data store. Calculation engine 26 may be configured toperform many different operations.

FIG. 2 illustrates an overview of a spreadsheet system 200 for accessingmultidimensional data through the use of cube functions, in accordancewith aspects of the invention. As illustrated, system 200 includes aserver 202, which includes and/or is coupled to a multidimensionaldatabase 204, such as an OLAP data store. Server 202 is coupled toclient 206 through a network connection. Client 206 includes aspreadsheet application 208. Spreadsheet application 208 includesspreadsheet cells 210. Typically, an active cell 212, which ishighlighted by the dark border, is the cell within the spreadsheet thatis currently being acted upon. For example, the user is entering a cubefunction into the cell. Zero or more of the spreadsheet cells maycontain a cube function which is directed at interacting with andretrieving data from the multidimensional database 204. According to oneembodiment, each cube function includes a Connection parameter 214 thatidentifies the multi-dimensional database to access. According to oneembodiment, the user may select a UI element, such as a dropdown, tochoose from a list of available connections to OLAP cubes. The selectedvalue is then included within the cube function.

Communication between the spreadsheet application and the OLAP database204 may be accomplished using MDX. Any other language, however, may beutilized that can communicate with an OLAP database. Furthermore,although the application is described herein as a spreadsheet, it willbe appreciated that other applications, such as word processingapplications that include spreadsheet cells, as well as otherapplications utilizing cells, may utilize the cube functions describedherein. According to another embodiment, the cube functions may bemaintained separately from an application and may be accessed through anAPI.

Generally, a user may enter cube functions within one or more of thecells within spreadsheet 208 to fetch data from the OLAP database 204.The queries to retrieve the data from the OLAP cube(s) that are sent maybe MDX expressions. Each of the cube functions includes connectioninformation that specifies the appropriate database.

The cells within the spreadsheet that contain cube functions appear tothe user to behave as any other cell within the spreadsheet thatcontains a formula. Generally, formulas within the spreadsheet caninclude the following: cube functions as defined herein that directlyquery an OLAP cube; dependent cube functions that query an OLAP cube butthat also require the results of a different query as one of theirarguments; standard spreadsheet functions that have a dependency on thevalues returned by the cube functions; and standard spreadsheetfunctions that have no dependency.

If each of these cube functions is independently executed within thespreadsheet then there may be a large number of small queries againstthe OLAP server. This could result in diminished performance for thespreadsheet application. According to one embodiment, the queries arecombined into fewer queries such that fewer queries are made to the OLAPserver.

The spreadsheet cell calculations may be performed asynchronously. Inother words, while data is being fetched from the OLAP server thecalculations may continue within the other cells. Therefore, thecalculations proceed for the cells that have no dependency on the resultset, but are delayed for cells that are dependent. If the cell has nodependency on a query, the cell will get its value right away (218). Ifthe cell has a dependency on a query, the cell is filled with atemporary error value of “#GETTING DATA . . . ” (216) and thecalculation proceeds to the next cell in the chain. This error shows theuser that an action is being performed that relates to the cell.

When all of the cells have been evaluated, the spreadsheet triggers theaggregated query(s) needed to obtain data. This query is runasynchronously whenever possible. Asynchronous query processing may bedesired so that the query won't block the spreadsheet applications UIthread and users can continue to work with the UI and can even abort thequery when it's taking too long. As the values arrive for the cells thatdisplay the #GETTING_DATA . . . 216 error message, the error message isreplaced with the fetched external data values and the calc is triggeredfor the cells that were dependent on the value that came in.

FIG. 3 illustrates a system 300 for interfacing with OLAP cubes fromcells of a spreadsheet application, in accordance with aspects of theinvention. System 300 in this embodiment includes a server 302 whichcorrespondingly has one or more databases stored thereupon 304. System300 may optionally include a network 306 such as a LAN, WAN, theInternet or other network which server 302 may be coupled to.

System 300 includes client 308. Client 308 includes a communicationmodule 310 that is coupled to a spreadsheet application 312.Furthermore, communication module 310 is coupled to the network 306.Communication module 310 may also be directly coupled to server 302and/or directly to OLAP cube(s) 304.

When a user configures a new spreadsheet in spreadsheet application 312,they may chose from a list of OLAP cubes 304 to which it may connect.Alternatively, the user may type in the location of an OLAP cube to beconnected. This link may then be given a connection name, such that thisname is used by a query module 311 to construct a query for the namedOLAP cube. Query module 311 is configured to receive the cubefunction(s) from one or more cells within the spreadsheet applicationand then construct an MDX query that will be passed on to server 302(via communication module 310) to be interpreted. The appropriate cube304 is then queried and, in response to the query, returns data from thedatabase relating to the query to communication module 310.Communication module 310 then passes the data to the spreadsheetapplication 312, which in turn fills in the cell(s) with the data. Whenother cells within the spreadsheet depend upon the returned data, thosecells may then be updated.

Communication module 310 may be located on client 308, however it mayalso be included on server 302 or may be included in cube(s) 304, amongother locations. Communication module 310 is typically provided bycube(s) 304 such that the client 308 and spreadsheet application 312 maycommunicate with the cube(s) 304. In one embodiment, communicationmodule 310 may comprise a dynamic-link library (DLL) that is provided(and configured) by the particular linked cube.

While query module 311 is shown as being separate from spreadsheetapplication 312, it may be included within the spreadsheet application312. The location of query module 311 may also be other than in theclient 308, such as within the server 302, or at some remote location.

FIG. 4 illustrates cube functions that may be utilized to connect with amultidimensional database, in accordance with aspects of the invention.

As illustrated, FIG. 4 includes the following cube functions: cube valuefunction 410; cube member function 420; cube set function 430; cuberanked member 440; cube KPI member 450; cube member property 460; andcube set count 470.

CubeValue (Connection [,Name1] [,Name2] . . . [,NameN]) function 410returns an aggregated value from the OLAP cube specified by theconnection parameter. The “Name” parameter is an optional parameter thatmay appear any number of times within the CubeValue function call. Forexample, one cube value function call may contain only one name, whileanother cube value function call may contain many names, separated bycommas. According to one embodiment, each name is the MDX name of amember or tuple (or tuple) within the cube. Alternatively, the namecould be a Set as defined by a CubeSet function as described below. Theprovided names are used as slicers to define the portion of the cube forwhich an aggregated value is to be returned. According to oneembodiment, if a Name is not supplied for any particular hierarchy, thenthe default member for that hierarchy is used. Similarly, when a Name isnot supplied for any measure, then the default measure for the cube isused. The Name parameter may also be a cell reference to another cell inthe spreadsheet that contains a unique MDX name in the cell's MDXproperty. Any cells that contain the CubeMember( ) function evaluate toa caption plus a unique MDX name in the cell's MDX property.

According to one embodiment, the tuple associated with any CubeValue isstored. This increases the efficiency to communicate with the OLAPserver about the portion of the cube that's associated with the desiredvalue. The following are some illustrative examples of the CubeValue( )function: CubeValue (“SALES”, “[Measures].[Profit]”, “[Time].[1999]”,“[All Product].[Beverages]”); CubeValue ($A$1, “[Measures].[Profit]”,D$12, $A23); and CubeValue (“SALES”, $B$7, D$12, $A23).

The CubeMember (Connection, Name [,Caption]) function 420 returns afully qualified unique MDX member name, or MDX tuple, as well as theuser-friendly caption for that member. According to one embodiment, whena tuple is returned, the user-friendly caption is the caption of thelast member in the tuple. Evaluating this function causes the cell todisplay the caption, while the cell's MDX property is set to the uniqueMDX member name or MDX expression for the tuple. The CubeMember functionreturns an error (#N/A) when the specified Name is not found within thespecified cube. The Name parameter is a string containing the MDX membername of a member within the cube. The CubeMember function validates thatthe provided name exists within the cube and then returns the uniquemember name associated with this member as the MDX property along withthat member's caption as the function's value. This argument may alsorepresent a tuple. When the argument is a tuple, the user specifies anarray of members using the spreadsheet's array syntax. The name argumentmay also be a reference to a cell that contains a member, or a cell thatcontains a string which evaluates to a unique MDX name, or a range ofcells which will evaluate to a tuple. The Caption parameter is anoptional string to be used as the caption in place of any caption thatwe obtain from the cube.

The following are some illustrative examples of the CubeMember( )function: CubeMember (“conn”, “[Customers].[USA]”); andCubeMember(“Finance”,[Account].[Accounts].[NetIncome].[OperatingProfit].[Gross Margin].[Net Sales].[Gross Sales]).

The CubeSet (Connection, Expression [,Caption] [,SortOrder] [,SortBy])function 430 defines a set that is to be built for the spreadsheet bythe OLAP server. The CubeSet function sends the Expression to the OLAPserver defined by the specified Connection. The server then creates aset by evaluating the Expression and returns that set to the spreadsheetprogram. The spreadsheet then uses the Caption as the function's displayvalue, while also setting the function's MDX (value) metadata to the MDXExpression that was sent to the OLAP server.

The Expression parameter is a string containing any arbitrary MDXexpression that can be evaluated by the OLAP server which results in aset of members or a set of tuples. An expression can also be a cellreference to a range that contains one or more cube members or tuples orsets. The Expression argument defines the items that are to be includedin the set.

The Caption parameter is the optional string to be used as the captionthat describes the set being created. When the caption is not provided,then the caption is set to a null string.

The SortOrder parameter is an optional integer argument whose value,according to one embodiment, is one of the following:

0 = SORT_NONE Leaves the Set in it's existing order 1 = SORT_ASCENDINGSorts Set in ascending order by SortBy field 2 = SORT_DESCENDING SortsSet in descending order by SortBy field 3 = SORT_ALPHA_ASCENDING SortsSet in Alpha ascending order 4 = SORT_ALPHA_DESCENDING Sorts Set inAlpha descending order 5 = SORT_NATURAL_ASCENDING Sorts Set in Naturalascending order 6 = SORT_NATURAL_DESCENDING Sorts Set in Naturaldescending order

The SortBy parameter is an optional expression string that defines thevalues on which sorting is to be done. For example, if a user would wantto obtain the city with the highest sales, then the Expression parameterdefines a set of cities, and the SortBy parameter is the sales measure.If the user wants to obtain the city with the highest population, thenthe Expression parameter defines a set of cities, and the SortByparameter is the population measure. The CubeSet function results in aset containing the items defined by the Expression parameter in thespecified SortOrder by SortBy.

The following are some illustrative examples of the CubeSet( ) function:CubeSet(“Finance”, “Order([Product].[Product].[ProductCategory].Members, [Measures].[Unit Sales], ASC)”, “Products”); andCubeSet(“Sales”, “[Product].[All Products].Children”, “Products”, 1,“[Measures].[Sales Amount]”).

The CubeRankedMember (Connection, Set, Rank [,Caption]) function 440returns the Nth (Rank) cube member (or tuple) in a Set, with the Captionas the display value, and a unique MDX name as the cell's MDX property.The Set is an expression that evaluates to a set within an OLAP cube. Inorder to obtain this set, the CubeSet( ) function may be used. Thismeans that Set may be a reference to a cell that contains a CubeSet( )function. In some OLAP servers, such as MICROSOFT CORPORATION'S AnalysisServices a set may be defined by an expression within curly braces: suchas “{[Item1].children}”. The CubeRankedMember function returns one ofthe elements within the Set. The Rank parameter is an integer thatdetermines the element within the set to return. When the Rank parameteris set to one then the CubeRanked Member function returns the firstvalue. When the Rank parameter equals two then the second value isreturned, and so on. For example, calling this function five differenttimes with the Rank parameter set to 1 thru 5 is one way to obtain the“Top 5” items from an OLAP cube. The Caption parameter is an optionalstring to be used as the caption in place of the member caption asobtained from the OLAP cube.

The following are some illustrative examples of the CubeRankedMember( )function: CubeRankedMember ($D$4, 1, “Top Month”); and CubeRankedMember(CubeSet (“SALES”, “Summer”, “[1999].[June]”, “[1999].[July]”,“[1999].[August]”), 1, “Top Month”).

The CubeKPIMember (Connection, KPIName, KPIComponent [,Caption])function 450 returns the KPI specified by the KPI name and the KPIComponent. According to one embodiment, this KPI includes a fullyqualified unique MDX expression as well as the user-friendly caption forthat KPI. Evaluating CubeKPIMember function causes the cell's valuewithin the spreadsheet to be set to the caption, while the cell's MDXproperty is set to the unique MDX expression for the requested KPI. TheCubeKPIMember function returns an empty string caption and has a NULLMDX property when the specified KPIName is not found within thespecified OLAP cube. The KPIName is the name of the KPI in the cube thatis to be queried. The KPIComponent is one of the following values:1—KPIValue; 2—KPIGoal; 3—KPIStatus; 4—KPITrend; 5—KPIWeight; and6—KPICurrentTimeMember. These are the components (properties) that makeup a KPI. This Caption is an optional string to be used as the captionin place of any caption that is obtained from the cube. To obtain thevalue of a KPI member with additional slicers as appropriate, the usermay use the CubeValue function, and references this KPI just as if itwere a member of any hierarchy in the cube.

The following are some illustrative examples of the CubeKPIMember( )function: CubeKPIMember (“Sales”, “MySalesKPI”, 1); and CubeKPIMember(“Sales”, “MySalesKPI”, 2, “Sales KPI Goal”).

The CubeMemberProperty (Connection, Name, Property) function 460 returnsthe value of a member property in the cube. The Name is the MDX uniquename of a member within the cube. The CubeMemberProperty functionvalidates that the name exists within the cube and then returns thespecified property for this member. The Property is the MDX name of theproperty to be returned or a reference to a cell that contains the nameof the property.

The following are some illustrative examples of the CubeMemberProperty() function: CubeMemberProperty (“Sales”, “[Time].[Fiscal].[1999]”,$A$3); and CubeMemberProperty (“Sales”, “[Store].[MyFavoriteStore]”,“[Store].[Store Name].[Store Sqft]”).

The CubeSetCount(Connection, Set) function 470 returns the number ofitems in the Set. The Set is an expression that evaluates to a set asdefined by the CubeSet function. In many cases, the cell will contain aCubeSet function. The following are some illustrative examples of theCubeSetCount( ) function: CubeSetCount (A3) and CubeSetCount (CubeSet(connection1, expression1, caption)).

When the Cube function is CubeSet or CubeSetCount, the MDX property isthe MDX expression that is sent to the server for evaluation. It is thesame as the Expression argument for the CubeSet function. For example:Cell C3 contains: =CubeSet(“conn”, “[Customers].children”); Cell C4contains: =CubeSetCount(C3); Range (“C3”).MDX=“[Customers].children” andRange (“C4”).MDX=“[Customers]. children.”

When the Cube function is CubeMember, CubeRankedMember, CubeValue,CubeKPIMember or CubeMemberProperty, then the MDX property provides atuple as returned from the server representing the unique name of themember or tuple defined by the function. For example: Cell C3 contains:=CubeMember (“conn”, “[Customers].[USA]”); Cell C4 contains:=CubeValue(“conn”, “[Bicycles]”, “[WA]”); Range (“C3”).MDX=(<uniquemember name for USA>) as returned by server; and Range(“C4”).MDX=(<tuple containing unique member names for Bicycles and WA>).

When reading the discussion of the routines presented herein, it shouldbe appreciated that the logical operations of various embodiments areimplemented (1) as a sequence of computer implemented acts or programmodules running on a computing system and/or (2) as interconnectedmachine logic circuits or circuit modules within the computing system.The implementation is a matter of choice dependent on the performancerequirements of the computing system implementing the invention.Accordingly, the logical operations illustrated and making up theembodiments of the described herein are referred to variously asoperations, structural devices, acts or modules. These operations,structural devices, acts and modules may be implemented in software, infirmware, in special purpose digital logic, and any combination thereof.

FIG. 5 displays an operational flow 500 for using cube functions withina spreadsheet, in accordance with aspects of the invention. After astart block, the process flows to define operation 510, which defines amultidimensional database. A database is defined or created when theparticular coordinates are defined and saved, and the data fieldspopulated. Typically, the multidimensional databases are OLAP databasesthat have already been created and populated. In this case, the selecteddatabases are coupled to the spreadsheet at operation 530.

Moving to operation 520, a spreadsheet is created that includes cubefunctions. Generally, a user may create a spreadsheet from scratch inwhich all of the cells within the spreadsheet are defined, but,typically, a user may start with a spreadsheet that has at least beenpartially created. According to one embodiment of the invention, a cellmay include zero or more of the following cube functions: a cube memberfunction; a cube value function; a cube set function; a cube rankedmember function; a cubed KPI member function; a cube member propertyfunction; and a cube set count function as described more fully herein.

Flowing to operation 530, the spreadsheet is coupled to the database.When the spreadsheet is created and particular cells are defined withinthe spreadsheet to include cube functions, different databases may belinked to the information in that cell and/or spreadsheet. In this way,each cell containing a cube function may fetch data from the appropriateOLAP data store.

Transitioning to operation 540, queries are created for the cubefunctions within the cells of the spreadsheet. According to oneembodiment, the queries are in the form of MDX queries.

Moving to operation 550, the queries are used to fetch the data relatingto the cube functions from one or more multidimensional data stores. Thecells within the spreadsheet may then be populated with the fetcheddata.

At operation 560, the results of the queries and any calculations thatwere performed may be displayed to the user. The process then moves toan end block and returns to processing other actions.

FIGS. 6 and 7 show an exemplary spreadsheet that includes cube functionsbefore evaluation and after evaluation, in accordance with aspects ofthe invention. FIG. 6 shows the cube function text within the cells.FIG. 7 shows the result of calculating the cube functions.

As illustrated, each cell within spreadsheet 600 and 700 includes a cubefunction. Including a cube function within each cell of the spreadsheetis merely for illustrative purposes. The spreadsheet may have zero ormore cube functions contained within the cells. For example, aspreadsheet may include only a single cube function to obtain a singlevalue from an OLAP cube.

Referring to a few of the cells within spreadsheet 600 it can be seenthat cell A3 includes the cube member function that connects to thefinance database and includes an MDX expression“[Account].[Accounts].[Net Income].[Operating Profit].[GrossMargin].[Net Sales].[Gross Sales].” As can be seen referring tospreadsheet 700 in FIG. 7, cell A3 is displayed as “Gross Sales.” CellB3 includes a cube value function that obtains the values from the $A$1,$B$1, B$2, and $A3 that results in an aggregated value of 3,200,477.00as displayed in cell B3 of spreadsheet 700. As illustrated each cubefunction has connection information to the “Finance” OLAP cube. Asdiscussed above, however, each spreadsheet may have zero or more cubeconnections. For example, one cell could include a cube function toobtain data from a first OLAP cube and another cell could include a cubefunction to obtain data from a second store.

Using cube functions within the spreadsheet allows the users to insertand/or delete rows and columns without interfering with the data'sability to be refreshed.

The above specification, examples and data provide a completedescription of the manufacture and use of the composition of theinvention. Since many embodiments of the invention can be made withoutdeparting from the spirit and scope of the invention, the inventionresides in the claims hereinafter appended.

1-26. (canceled)
 27. A computer-readable storage medium havingcomputer-executable instructions for interacting with an OLAP cube,comprising: parsing a cube function having parameters; wherein the cubefunction is included within a cell of a spreadsheet and wherein the cubefunction is directed at obtaining data from an OLAP cube; wherein thecube function is from cube functions comprising: a cube member functionthat identifies an item in the OLAP cube and returns a name for themember; a cube value function that identifies an aggregated value fromthe OLAP cube; a cube set function that defines a set that is to bebuilt for the spreadsheet by an OLAP server storing the OLAP cube; and amember property function that identifies a value of a member property inthe OLAP cube and a count relating to a set within the OLAP cube;determining an OLAP cube from which to obtain data from one of theparameters of the cube function; and fetching the data from thedetermined OLAP cube in response to the parameters of the cube function.28. The computer-readable storage medium of claim 27, whereindetermining the OLAP cube from which to obtain data comprises locating aconnection name that determines the OLAP cube to obtain data from. 29.The computer-readable storage medium of claim 27, wherein parsing thecube function comprises parsing the parameters to locate one or morenames, wherein the names identify at least one of a member and a tuplewithin the OLAP cube; and wherein each of the located names is used aslicer to define a portion of the OLAP cube for which an aggregatedvalue is to be fetched from the OLAP cube.
 30. The computer-readablestorage medium of claim 27, wherein parsing the cube function comprisesparsing the parameters to locate a name that identifies a member of theOLAP cube and using the name to fetch at least one of a unique MDXmember name, a MDX tuple, and a caption for the member of the OLAP cube.31. The computer-readable storage medium of claim 27, wherein parsingthe cube function comprises parsing the parameters to locate anexpression that identifies a set that is at least one of a set ofmembers within the OLAP cube and a set of tuples within the OLAP cube;and then using the query to fetch the set from the OLAP cube; and when asort parameter exists within the parameters; using the sort parameter tosort the set.
 32. The computer-readable storage medium of claim 27,wherein parsing the cube function comprises parsing the parameters tolocate a set and a rank; wherein the rank is used to fetch at least oneof the elements from the set.
 33. The computer-readable storage mediumof claim 27, wherein parsing the cube function comprises parsing theparameters to locate a KPI name and a KPI component; wherein the KPIname and the KPI component are used to fetch a member from the OLAPcube.
 34. The computer-readable storage medium of claim 27, whereinparsing the cube function comprises parsing the parameters to locate aset and wherein a number of the members within the set is determined.35. The computer-readable storage medium of claim 27, wherein parsingthe cube function comprises parsing the parameters to locate a name anda member; wherein the name and member are used to obtain a value of amember property in the OLAP cube.
 36. A system for interacting with amultidimensional database from a spreadsheet, comprising: a processorand a computer-readable storage medium; and a spreadsheet applicationthat is coupled to a network and is configured to perform steps,comprising: parsing a cube function having parameters that is locatedwithin a cell of the spreadsheet to generate an MDX query; wherein theMDX query locates data within a multidimensional database; wherein thecube function is from cube functions comprising a cube member functionthat identifies an item in the multidimensional database and returns aname for the member; a cube value function that identifies an aggregatedvalue from the multidimensional database; a cube set function thatdefines a set that is to be built for the spreadsheet by an OLAP serverstoring the multidimensional database; and a member property functionthat identifies a value of a member property in the multidimensionaldatabase and a count relating to a set within the multidimensionaldatabase; determining the multidimensional database to query based on atleast one of the parameters of the cube function; querying a serveridentified by the determined multidimensional database using the MDXquery; receiving data from the server that is returned in response tothe MDX query; and updating the cell and any other dependent cells inresponse to the received data.
 37. The system of claim 36, wherein theMDX query requests at least one of the following from themultidimensional database: a ranked member; a KPI member; a memberproperty of an item within the multidimensional database and a countrelating to a set within the multidimensional database.
 38. The systemof claim 37, wherein different cells of the spreadsheet request datafrom different multidimensional databases.
 39. The system of claim 37,wherein parsing the cube function comprises parsing the parameters tolocate names, wherein the names identify a member and a tuple within theOLAP cube; and wherein each of the located names is used a slicer todefine a portion of the OLAP cube for which an aggregated value is to befetched from the OLAP cube.
 40. A method for interacting with an OLAPcube, comprising: parsing a cube function having parameters; wherein thecube function is included within a cell of a spreadsheet and wherein thecube function is directed at obtaining data from an OLAP cube; whereinthe spreadsheet includes cells that do not include data from amultidimensional database; determining an OLAP cube from which to obtainthe data by accessing one of the parameters of the cube function; andfetching the data from the determined OLAP cube in response to theparameters of the cube function.
 41. The method of claim 40, whereinparsing the cube function comprises parsing the parameters to locatenames that identify at least one of a member and a tuple within the OLAPcube; and wherein each of the located names is used a slicer to define aportion of the OLAP cube for which an aggregated value is to be fetchedfrom the OLAP cube.
 42. The method of claim 40, wherein parsing the cubefunction comprises parsing the parameters to locate a name that is usedto fetch at least one of a unique MDX member name, a MDX tuple, and acaption for the member of the OLAP cube.
 43. The method of claim 40,wherein parsing the cube function comprises parsing the parameters tolocate an expression that identifies a set that is at least one of a setof members within the OLAP cube and a set of tuples within the OLAPcube; and then using the query to fetch the set from the OLAP cube; andwhen a sort parameter exists within the parameters; using the sortparameter to sort the set.
 44. The method of claim 40, wherein parsingthe cube function comprises parsing the parameters to locate a set and arank; wherein the rank is used to fetch at least one of the elementsfrom the set.
 45. The method of claim 40, wherein parsing the cubefunction comprises parsing the parameters to locate a set and wherein anumber of the members within the set is determined.
 46. The method ofclaim 40, wherein parsing the cube function comprises parsing theparameters to locate a name and a member; wherein the name and memberare used to obtain a value of a member property in the OLAP cube.